<?php
namespace app\service;
use think\facade\Db;

class billService{
    //查询账单数据条数
    public function billNumber(){
        return Db::name('bill')->select();
    }
    //根据小区编号查询小区名称
    public function communityName($comid){
        return Db::name('community')->where('community_id' , $comid)->find();
    }
    //查询小区信息
    public function communityId(){
        return Db::name('community')->select();
    }
    //查询指定小区房屋信息
    public function roomIdInfo($community_id){
        return Db::name('roominfo')->where('community_id',$community_id)->order('id asc')->select();
    }
    //查询完整门牌号
    public function roomAddress($community_id,$roomid){
        return Db::name('roominfo')->where('community_id',$community_id)->where('address',$roomid)->find();
    }
    public function roominfo($community_id,$group,$building,$unit,$room){
        return Db::name('roominfo')
                ->where('community_id',$community_id)
                ->where('group',$group)
                ->where('building',$building)
                ->where('unit',$unit)
                ->where('room',$room)
                ->find();
    }
    public function costtype($com){
        $usertype=$com["usertype"];
        if($usertype=="0"){
            return Db::name('costtype')->distinct("type")->select();
        }
        if($usertype=="1"){
            return Db::name('costtype')->where("property_id",$com["property_id"])->select();
        }
        if($usertype=="2"){
            $communityinfo=Db::table("community")->where("community_id",$com["community_id"])->find();
            return Db::name('costtype')->where("property_id",$communityinfo["property_id"])->select();
        }
        if($usertype=="3"){
            return Db::name('costtype')->where("property_id","in",$com["property_id"])->select();
            /*$communityinfo=Db::table("community")->where("community_id",$com["community_id"])->find();
            return Db::name('costtype')->where("property_id","in",$com["property_id"])->distinct("type")->select();*/
        }
        
    }
    //添加账单
    public function billAdd($data){
        return Db::name('bill')->insert($data);
    }
    //查询指定ID账单数据
    public function billIdinfo($id){
        return Db::name('bill')->where('id',$id)->find();
    }
    //修改指定账单数据
    public function billEdit($data){
        return Db::name('bill')->update($data);
    }
    //查询指定id是否存在
    public function billEntryIdInfo($data){
        return Db::name('bill')->where('id','in',$data)->select();
    }
    //删除指定的数据
    public function deleteBillInfo($data) {
        $info=Db::name('bill')->where('id', $data)->find();
        Db::table("billdel")->insert($info);
        return Db::name('bill')->where('id', $data)->delete();
    }
    //查询token
    public function billinfoToken($community_id){
        $property_id = Db::name('community')->where('community_id',$community_id)->value('property_id');
        return Db::name('property')->where('id',$property_id)->value('accredit');
    }
    //----------------------------------------通过Excel文件批量导入账单--------------------------------------------------
    //通过房间门牌号查询对应房间编号
    public function roomId($community_id,$roomname){
        return Db::name('roominfo')->where('community_id',$community_id)->where('address',$roomname)->find();
    }
    
    //通过小区名称查询对应的小区id
    public function communityNameId($communityName){
        return Db::name('community')->where('community_name', $communityName)->find();
    }
    //判断账单是否重复
    public function billcosttype($community_id,$out_room_id,$cost_type,$acct_period){
        return Db::name('bill')->where('community_id',$community_id)->where('out_room_id',$out_room_id)->where('cost_type',$cost_type)->where('acct_period',$acct_period)->find();
    }
    //通过Excel插入数据
    public function billExcelAdd($data){
        return Db::name('bill')->insertGetId($data);
    }
    //导出条件查询
    public function billWhereExport($community_id,$where,$gmtstart,$gmtend){
        return Db::name('bill')->where('community_id', 'in', $community_id)->where('gmt_payment' , '>=' , $gmtstart)->where('gmt_payment' , '<=' , $gmtend)->where($where)->select();
    }
    //----------------------------------------多条件查询账单信息--------------------------------------------------
    //查询房号
    public function whereRoom($community_id,$whereroom){
        return Db::name('roominfo')->where('community_id','in',$community_id)->where($whereroom)->column('out_room_id');
    }
    //多条件查询
    public function billWhereList($community_id,$page,$where,$gmtstart,$gmtend){
        $sjwhere = $where;
        $where['gmtstart'] = $gmtstart;
        $where['gmtend'] = $gmtend;
        return Db::name('bill')
                ->where('community_id', 'in', $community_id)
                ->where($sjwhere)
                ->where('gmt_payment' , '>=' , $gmtstart)
                ->where('gmt_payment' , '<=' , $gmtend)
                ->order('id desc')->paginate(200, false, [
                    'page'  =>  $page,
                    'query' =>  $where
                ]);
    }
     //多条件查询
    public function invoiceWhereList($community_id,$page,$where,$gmtstart,$gmtend){
        $sjwhere = $where;
        $where['gmtstart'] = $gmtstart;
        $where['gmtend'] = $gmtend;
        return Db::name('bill')
                ->where('community_id', 'in', $community_id)
                ->where($sjwhere)
                ->where('gmt_payment' , '>=' , $gmtstart)
                ->where('gmt_payment' , '<=' , $gmtend)
                ->order('invoiceTime desc')->paginate(100, false, [
                    'page'  =>  $page,
                    'query' =>  $where
                ]);
    }
    //多条件查询
    public function billWhereList2($community_id,$page,$where,$gmtstart,$gmtend){
        $sjwhere = $where;
        $where['gmtstart'] = $gmtstart;
        $where['gmtend'] = $gmtend;
        return Db::name('bill')
                ->where('community_id', 'in', $community_id)
                ->where($sjwhere)
                ->where('gmt_payment' , '>=' , $gmtstart)
                ->where('gmt_payment' , '<=' , $gmtend)
                ->order('id desc')->paginate(1000, false, [
                    'page'  =>  $page,
                    'query' =>  $where
                ]);
    }
    //多条件查询
    public function unpaybillWhereList($community_id,$page,$where,$gmtstart,$gmtend){
        $sjwhere = $where;
        $where['gmtstart'] = $gmtstart;
        $where['gmtend'] = $gmtend;
        return Db::name('bill')
                ->where('community_id', 'in', $community_id)
                ->where($sjwhere)
                ->where('gmt_payment' , '>=' , $gmtstart)
                ->where('gmt_payment' , '<=' , $gmtend)
                ->order('id desc')->paginate(100, false, [
                    'page'  =>  $page,
                    'query' =>  $where
                ]);
    }
    //查询条数
    public function billWhereNumber($community_id,$where,$gmtstart,$gmtend){
        return Db::name('bill')->where('community_id', 'in', $community_id)->where('gmt_payment' , '>=' , $gmtstart)->where('gmt_payment' , '<=' , $gmtend)->where($where)->count();
    }
    //查询总金额
    public function billWhereSum($community_id,$where,$gmtstart,$gmtend){
        return Db::name('bill')->where('community_id', 'in', $community_id)->where('gmt_payment' , '>=' , $gmtstart)->where('gmt_payment' , '<=' , $gmtend)->where($where)->sum('bill_entry_amount');
    }
    //----------------------------------------账单一键收款--------------------------------------------------
    //一键收款
    public function paytype(){
        return Db::name('paytype')->select();
    }
    public function billEntryIdInfoSum($id){
        return Db::name('bill')->where('id','in',$id)->sum('bill_entry_amount');
    }
    //----------------------------------------账单一键收款--------------------------------------------------
    //同步支付宝
    public function billStatus($data){
        return Db::name('bill')->where('bill_entry_id',$data)->setField('tongbu','1');
    }
    //异步回调修改
    //查询指定id是否存在
    public function billApiId($data){
        if (is_array($data)) {
            return Db::name('bill')->where('bill_entry_id', 'in', $data)->select();
        }else{
            return Db::name('bill')->where('bill_entry_id', $data)->find();
        }
    }
    //修改账单信息
    public function billApiEdit($info){
        return Db::name('bill')->update($info);
    }
    //----------------------------------------账单打印--------------------------------------------------
    //查询打印机终端
    public function communityMachine($community_id){
        return Db::name('community')->where('community_id',$community_id)->find();
    }
    //根据房屋编号查询业主姓名
    public function billOwnerName($out_room_id){
        return Db::name('roominfo')->where('out_room_id',$out_room_id)->value('owner_name');
    }
    //根据物业ID查询物业信息
    public function propertyIdName($id){
        return Db::name('property')->where('id',$id)->value('name');
    }
    //----------------------------------------账单发票--------------------------------------------------    
    //根据发票app_key查询物业ID
    public function propertyFpAppKey($fp_app_key){
        return Db::name('property')->where('fp_app_key',$fp_app_key)->value('id');
    }
    
    public function serviceFpAppKey($fp_app_key){
        return Db::name('service')->where('fp_app_key',$fp_app_key)->value('id');
    }

    public function serviceFpAppKeyV2($fp_app_key){
        return Db::name('service')->where('fp_app_key_v2',$fp_app_key)->value('id');
    }
    //修改指定ID物业信息
    public function propertyUpdate($data){
        return Db::name('property')->update($data);
    }
    //修改指定ID物业信息
    public function serviceUpdate($data){
        return Db::name('service')->update($data);
    }
    public function serviceInfo($id){
        return Db::name('service')->where('id',$id)->find();
    }
    //查询物业信息
    public function propertyInfo($id){
        return Db::name('property')->where('id',$id)->find();
    }
    //查询税率
    public function taxRate($type){
        return Db::name('costtype')->where('type',$type)->value('tax_rate');
    }
    //查询缴费类型税率
    public function taxRateCommunity($type,$community_id){
        
        $communityinfo=Db::table("community")->where("community_id",$community_id)->find();
        
        return Db::name('costtype')->where('type',$type)->where("property_id",$communityinfo["property_id"])->value('tax_rate');
    }
    public function goodsCodeCommunity($type,$community_id){

        $communityinfo=Db::table("community")->where("community_id",$community_id)->find();

        return Db::name('costtype')->where('type',$type)->where("property_id",$communityinfo["property_id"])->value('goodsCode');
    }
    
    public function getCostTypeInfo($type,$community_id){

        $communityinfo=Db::table("community")->where("community_id",$community_id)->find();

        return Db::name('costtype')->where('type',$type)->where("property_id",$communityinfo["property_id"])->find();
    }
    //根据发票单号查询账单
    public function numBillInfo($invoice_serial_num){
        return Db::name('bill')->where('invoice_serial_num',$invoice_serial_num)->where('invoice_type',1)->select();
    }
    //----------------------------------------微信缴费--------------------------------------------------    
    public function billRoomIdList($out_room_id){
        return Db::name('bill')->where('out_room_id',$out_room_id)->where('tongbu',1)->where('status','WAIT_PAYMENT')->select();
    }
}